package org.emet.service;

import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.TreeMap;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.hssf.util.HSSFRegionUtil;
import org.apache.poi.ss.util.CellRangeAddress;
import org.emet.dao.CarriageDao;
import org.emet.dao.ChargeRecordDao;
import org.emet.dao.ServiceItemDao;
import org.emet.entity.biz.Carriage;
import org.emet.entity.biz.ChargeItem;
import org.emet.entity.biz.ChargeRecord;
import org.emet.entity.biz.Registration;
import org.emet.help.CommonUtils;
import org.emet.jee.core.utils.CurrencyUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

@Service
@Transactional
public class ReportService {
	static String[] fixedHeader = new String[] { "组", "火化证号", "逝者姓名", "性别",
			"年龄", "遗体接运地点", "火化时间", "家属姓名", "实际收费", "联系电话" };

	@Autowired
	private ServiceItemDao serviceItemDao;
	@Autowired
	private ChargeRecordDao chargeRecordDao;
	@Autowired
	private CarriageDao carriageDao;

	@Transactional(readOnly = true)
	public HSSFWorkbook generateMonthlyBizReport(int year, int month) {
		List<ChargeRecord> regInfoList = chargeRecordDao.find(
				"from ChargeRecord where year(chargeDate) = ? and month(chargeDate) = ? ", year, month);

		HSSFWorkbook workbook = new HSSFWorkbook();
		HSSFSheet sheet = workbook.createSheet(generateStringWithDateTime(year
				+ "年" + month + "月业务报表"));
		sheet.setAutoFilter(CellRangeAddress.valueOf("A3"));
		sheet.setAutoFilter(CellRangeAddress.valueOf("C3"));
		sheet.setAutoFilter(CellRangeAddress.valueOf("D3"));
		sheet.setAutoFilter(CellRangeAddress.valueOf("E3"));
		sheet.setAutoFilter(CellRangeAddress.valueOf("F3"));
		sheet.setAutoFilter(CellRangeAddress.valueOf("G3"));
		sheet.setAutoFilter(CellRangeAddress.valueOf("F3"));

		HSSFCellStyle style2 = getStyle(workbook);

		HSSFFont font2 = workbook.createFont();
		font2.setFontName("宋体");
		font2.setFontHeightInPoints((short) 8);
		font2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
		style2.setFont(font2);

		// 产生固定表格标题
		final int headerRowIndex = 2;
		HSSFRow headerRow = sheet.createRow(headerRowIndex);
		int curHeadCell = 0;
		for (; curHeadCell < fixedHeader.length; curHeadCell++) {
			sheet.autoSizeColumn(curHeadCell);
			HSSFCell cell = headerRow.createCell(curHeadCell);
			cell.setCellStyle(style2);
			cell.setCellValue(fixedHeader[curHeadCell]);
		}
		HSSFFont font3 = workbook.createFont();
		font3.setColor(HSSFColor.BLUE.index);

		int rowIndex = 3;
		for (int j = 0; j < (regInfoList.size()); j++) {
			HSSFRow row = sheet.createRow(rowIndex++);
			ChargeRecord chargeRecord = regInfoList.get(j);
			final Registration reg = chargeRecord.getRegistration();

			int cellIndex = 0;
			// "组"
			HSSFCell teamCell = row.createCell(cellIndex++);
			teamCell.setCellStyle(style2);
			teamCell.setCellValue(reg.getBizTeam());

			// 火化证号
			HSSFCell bizNoCell = row.createCell(cellIndex++);
			bizNoCell.setCellStyle(style2);
			bizNoCell.setCellValue(reg.getBizNo());

			//dead name
			HSSFCell deadCell = row.createCell(cellIndex++);
			deadCell.setCellStyle(style2);
			deadCell.setCellValue(reg.getDeadName());

			//gender
			HSSFCell genderCell = row.createCell(cellIndex++);
			genderCell.setCellStyle(style2);
			genderCell.setCellValue(CommonUtils.getDataItemDisplayText(2L, reg.getdGender()));

			//age
			HSSFCell ageCell = row.createCell(cellIndex++);
			ageCell.setCellStyle(style2);
			ageCell.setCellValue(reg.getAge());

			//carriage address
			HSSFCell addrCell = row.createCell(cellIndex++);
			addrCell.setCellStyle(style2);
			addrCell.setCellValue(reg.getCarriageAddr());

			//cremate day
			HSSFCell cremationCell = row.createCell(cellIndex++);
			cremationCell.setCellStyle(style2);
			final Date cremationDate = reg.getCremationDate();
			if (cremationDate == null) {
				cremationCell.setCellValue("");
			} else {
				cremationCell.setCellValue(cremationDate);
			}

			//relative name
			HSSFCell clientCell = row.createCell(cellIndex++);
			clientCell.setCellStyle(style2);
			clientCell.setCellValue(reg.getClient());

			//Paid fee
			HSSFCell feeCell = row.createCell(cellIndex++);
			feeCell.setCellStyle(style2);
			feeCell.setCellValue(chargeRecord.getPaidFee());

			//Phone
			HSSFCell phoneCell = row.createCell(cellIndex++);
			phoneCell.setCellStyle(style2);
			phoneCell.setCellValue(reg.getPhone());
		}

		List<String> serviceItemNames = serviceItemDao.find("select name from ServiceItem order by id");
		for (String name : serviceItemNames) {
			sheet.autoSizeColumn(curHeadCell);
			HSSFCell cell = headerRow.createCell(curHeadCell);
			cell.setCellStyle(style2);
			cell.setCellValue(name);
			curHeadCell++;
		}

		double totalFee = 0.00;
		int startRowIndex = headerRowIndex + 1;
		for (ChargeRecord rec : regInfoList) {
			List<ChargeItem> items = rec.getItems();
			int i = fixedHeader.length;
			HSSFRow row = sheet.getRow(startRowIndex++);
			for (String name : serviceItemNames) {
				BigDecimal fee = findTotalFee(items, name);
				HSSFCell cell = row.createCell(i++);
				cell.setCellStyle(style2);
				cell.setCellValue(fee.doubleValue());
			}

			totalFee += rec.getPaidFee();
		}

		HSSFRow statistrow = sheet.createRow(0);
		HSSFCell statTotalCell = statistrow.createCell(fixedHeader.length + serviceItemNames.size() - 2);
		statTotalCell.setCellValue("总共费用:" + totalFee);

		// 设置最顶端标题
		HSSFRow titleRow = sheet.createRow(1);
		HSSFCell titleCell = titleRow.createCell(0);
		titleCell.setCellValue("殡仪馆" + year + "年" + month + "月业务报表");
		HSSFCellStyle titleStyle = workbook.createCellStyle();
		titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		HSSFFont titleFont = workbook.createFont();
		titleFont.setFontName("黑体");
		titleFont.setFontHeightInPoints((short) 16);
		titleStyle.setFont(titleFont);
		titleCell.setCellStyle(titleStyle);
		sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, fixedHeader.length + serviceItemNames.size() - 1));

		return workbook;
	}

	private HSSFCellStyle getStyle(HSSFWorkbook workbook) {
		HSSFCellStyle style2 = workbook.createCellStyle();
		style2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		style2.setBorderRight(HSSFCellStyle.BORDER_THIN);
		style2.setBorderTop(HSSFCellStyle.BORDER_THIN);
		style2.setAlignment(HSSFCellStyle.ALIGN_LEFT);
		style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		return style2;
	}

	@Transactional(readOnly = true)
	public HSSFWorkbook generateYearlyBizReport(int year) {
		// 产生固定表格标题
		String[] fixedHeaderStart = new String[] { "日期", "具数" };
		String[] fixedHeaderEnd = new String[] { "收入合计", "欠帐", "实收现金" };

		int startMon = 1;
		int endMon = 12;

		List<String> serviceItemNames = serviceItemDao.find("select name from ServiceItem order order by id");
		HSSFWorkbook workbook = new HSSFWorkbook();
		HSSFCellStyle style2 = getStyle(workbook);
		HSSFCellStyle moneyStyle = getMoneyStyle(workbook);
		for (int i = startMon; i <= endMon; i++) {
			HSSFSheet sheet = workbook.createSheet(i + "月");
			//head

			int headerRowIndex = 1;
			HSSFRow headerRow = sheet.createRow(headerRowIndex);
			int curHeadCell = 0;
			for (int x = 0; x < (fixedHeaderStart.length); x++) {
				HSSFCell cell = headerRow.createCell(curHeadCell);
				sheet.autoSizeColumn(curHeadCell);
				cell.setCellStyle(style2);
				cell.setCellValue(fixedHeaderStart[x]);
				curHeadCell++;
			}

			for (String name : serviceItemNames) {
				HSSFCell cell = headerRow.createCell(curHeadCell);
				sheet.autoSizeColumn(curHeadCell);
				cell.setCellStyle(style2);
				cell.setCellValue(name);
				curHeadCell++;
			}

			for (int j = 0; j < (fixedHeaderEnd.length); j++) {
				HSSFCell cell = headerRow.createCell(curHeadCell);
				sheet.autoSizeColumn(curHeadCell);
				cell.setCellStyle(style2);
				cell.setCellValue(fixedHeaderEnd[j]);
				curHeadCell++;
			}

			// data
			int dataRowStartIndex = 2;
			List<Integer> countBodyCountGroupByMonth = chargeRecordDao.countBodyCountGroupByMonth(year, i);
			List<TreeMap<String, Object>> dataList = chargeRecordDao.findGenerateBizNo(year, i);
			int j = 0;
			for (TreeMap<String, Object> treeMap : dataList) {
				int dataColStartIndex = 0;

				HSSFRow row = sheet.createRow(dataRowStartIndex++);

				HSSFCell dayCell = row.createCell(dataColStartIndex++);
				dayCell.setCellStyle(style2);
				dayCell.setCellValue((Integer) treeMap.get("dayStr"));

				HSSFCell bodyCountCell = row.createCell(dataColStartIndex);
				bodyCountCell.setCellStyle(style2);
				bodyCountCell.setCellValue(countBodyCountGroupByMonth.get(j++));

				dataColStartIndex += serviceItemNames.size() + 1;

				HSSFCell shouldPaidTotalFeeCell = row.createCell(dataColStartIndex++);
				shouldPaidTotalFeeCell.setCellStyle(moneyStyle);
				shouldPaidTotalFeeCell.setCellValue((Double) treeMap.get("should_paid_total_fee"));

				HSSFCell ownfeeCell = row.createCell(dataColStartIndex++);
				ownfeeCell.setCellStyle(moneyStyle);
				Double ownFee = (Double) treeMap.get("ownFee");
				if (ownFee < 0) {
					ownFee = 0d;
				}
				ownfeeCell.setCellValue(ownFee);

				HSSFCell paidFeeCell = row.createCell(dataColStartIndex++);
				paidFeeCell.setCellStyle(moneyStyle);
				paidFeeCell.setCellValue((Double) treeMap.get("paidFee"));
			}

			//  set title
			// 设置最顶端标题
			HSSFRow titleRow = sheet.createRow(0);
			HSSFCell titleCell = titleRow.createCell(0);
			titleCell.setCellValue(year + "年" + i + "月份火化收入汇总表");
			HSSFCellStyle titleStyle = workbook.createCellStyle();
			titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
			HSSFFont titleFont = workbook.createFont();
			titleFont.setFontName("黑体");
			titleFont.setFontHeightInPoints((short) 16);
			titleStyle.setFont(titleFont);
			titleCell.setCellStyle(titleStyle);
			sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, fixedHeaderStart.length + serviceItemNames.size()
					+ fixedHeaderEnd.length - 1));
		}

		for (int i = startMon; i <= endMon; i++) {
			int dataRowStartIndex = 2;

			Calendar ins = Calendar.getInstance();
			ins.set(Calendar.YEAR, year);
			ins.set(Calendar.MONTH, i - 1);
			int actualMaximum = ins.getActualMaximum(Calendar.DAY_OF_MONTH);

			HSSFSheet sheet = workbook.getSheetAt(i - 1);
			for (int k = 1; k <= actualMaximum; k++) {
				int chargeItemStartIndex = fixedHeaderStart.length;

				ins.set(Calendar.DAY_OF_MONTH, k);
				List<HashMap<String, Object>> findServiceItemOfDay = chargeRecordDao
						.findServiceItemOfDay(ins.getTime());

				if (findServiceItemOfDay.size() == 0) {
					continue;
				}
				for (String serviceItemName : serviceItemNames) {
					boolean found = false;
					for (HashMap<String, Object> entry : findServiceItemOfDay) {
						String chargeItemName = (String) entry.get("name");
						if (chargeItemName.equalsIgnoreCase(serviceItemName)) {
							HSSFRow row = sheet.getRow(dataRowStartIndex);
							Double totalFee = (Double) entry.get("totalFee");
							HSSFCell cell = row.createCell(chargeItemStartIndex);
							cell.setCellStyle(moneyStyle);
							cell.setCellValue(totalFee);
							found = true;
							break;
						}
					}
					if (!found) {
						HSSFRow row = sheet.getRow(dataRowStartIndex);
						HSSFCell cell = row.createCell(chargeItemStartIndex);
						cell.setCellStyle(moneyStyle);
					}
					chargeItemStartIndex++;
				}
				dataRowStartIndex++;
			}

			if (dataRowStartIndex != 2) {
				HSSFRow row = sheet.createRow(dataRowStartIndex);
				int maxCol = fixedHeaderStart.length + serviceItemNames.size() + fixedHeaderEnd.length;
				HSSFCell totalCell = row.createCell(0);
				totalCell.setCellStyle(style2);
				totalCell.setCellValue("合计:");

				char w = 'B';
				for (int col = 1; col < maxCol; col++) {
					HSSFCell cell = row.createCell(col);
					cell.setCellStyle(moneyStyle);
					cell.setCellFormula("SUM(" + w + "3:" + w + "" + (dataRowStartIndex) + ")");
					w += 1;
				}
			}
		}

		HSSFFormulaEvaluator.evaluateAllFormulaCells(workbook);
		return workbook;

	}

	private HSSFCellStyle getMoneyStyle(HSSFWorkbook workbook) {
		HSSFCellStyle style2 = workbook.createCellStyle();
		style2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		style2.setBorderRight(HSSFCellStyle.BORDER_THIN);
		style2.setBorderTop(HSSFCellStyle.BORDER_THIN);
		style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		style2.setDataFormat((short) 4);
		return style2;
	}

	private BigDecimal findTotalFee(List<ChargeItem> items, String name) {
		// for (ChargeItem chargeItem : items) {
		// if (chargeItem.getDetailName().equalsIgnoreCase(name))
		// return chargeItem.getTotalFee();
		// }
		return new BigDecimal(0);
	}

	@Transactional(readOnly = true)
	public String generateStringWithDateTime(String pattern) {
		return new SimpleDateFormat(pattern).format(new Date());
	}

	@Transactional(readOnly = true)
	public String generateStringWithDateTime(String pattern, Date date) {
		return new SimpleDateFormat(pattern).format(date);
	}

	@Transactional(readOnly = true)
	public HSSFWorkbook genernateCarriageReceipt(String title, Long carriageId) {
		Carriage carriage = carriageDao.get(carriageId);

		HSSFWorkbook workbook = new HSSFWorkbook();
		HSSFSheet sheet = workbook.createSheet();

		//For title
		HSSFRow titleRow = sheet.createRow(0);
		HSSFCell titleCell = titleRow.createCell(0);
		titleCell.setCellValue(title);

		HSSFCellStyle titleStyle = workbook.createCellStyle();
		titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		HSSFFont titleFont = workbook.createFont();
		titleFont.setFontName("黑体");
		titleFont.setFontHeightInPoints((short) 16);
		titleStyle.setFont(titleFont);
		titleCell.setCellStyle(titleStyle);

		sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 7));

		//# of business
		HSSFRow bizNoRow = sheet.createRow(2);
		HSSFCell bizNoCellLabel = bizNoRow.createCell(0);
		bizNoCellLabel.setCellValue("业务编号:");
		HSSFCell bizNoCellLabelContent = bizNoRow.createCell(1);
		bizNoCellLabelContent.setCellValue(carriage.getBizNo());

		HSSFCellStyle tableContentStyle = getStyle(workbook);
		HSSFCellStyle leftRightBoldCornerStyle = getLeftRightBorderBoldStyle(workbook);
		HSSFCellStyle topBorderBoldStyle = getTopBorderBoldStyle(workbook);
		HSSFCellStyle rightTopBorderBoldStyle = getRightTopBorderBoldStyle(workbook);
		HSSFCellStyle leftBorderBoldStyle = getLeftBorderBoldStyle(workbook);
		HSSFCellStyle rightBorderBoldStyle = getRightBorderBoldStyle(workbook);

		short rowHeight = 23;
		//Content of receipt table
		//First Row:
		HSSFRow firstRow = sheet.createRow(3);
		firstRow.setHeightInPoints(rowHeight);
		createCell(firstRow, 0, "逝者姓名", leftRightBoldCornerStyle);
		// createCell(firstRow, 1, carriage.getDeceadName(),
		// topBorderBoldStyle);
		createCell(firstRow, 2, "性别", topBorderBoldStyle);
		createCell(firstRow, 3, CommonUtils.getDataItemDisplayText(2L, carriage.getdGender()), topBorderBoldStyle);
		createCell(firstRow, 4, "年龄", topBorderBoldStyle);
		createCell(firstRow, 5, carriage.getdAge(), topBorderBoldStyle);
		createCell(firstRow, 6, "逝世原因", topBorderBoldStyle);
		createCell(firstRow, 7, carriage.getDeathReason(), rightTopBorderBoldStyle);

		//2nd row
		HSSFRow secondRow = sheet.createRow(4);
		secondRow.setHeightInPoints(rowHeight);
		createCell(secondRow, 0, "联系人姓名", leftBorderBoldStyle);
		createCell(secondRow, 1, carriage.getContactName(), tableContentStyle);
		createCell(secondRow, 2, "", tableContentStyle);
		sheet.addMergedRegion(new CellRangeAddress(4, 4, 1, 2));
		createCell(secondRow, 3, "联系电话", tableContentStyle);
		// createCell(secondRow, 4, carriage.getPhone(), tableContentStyle);
		createCell(secondRow, 5, "", tableContentStyle);
		sheet.addMergedRegion(new CellRangeAddress(4, 4, 4, 5));
		// createCell(secondRow, 6, "业务单位", tableContentStyle);
		//		createCell(secondRow, 7, carriage.getBizUnit(), rightBorderBoldStyle);

		//3rd row
		HSSFRow thirdRow = sheet.createRow(5);
		thirdRow.setHeightInPoints(rowHeight);
		createCell(thirdRow, 0, "接运时间", leftBorderBoldStyle);
		createCell(thirdRow, 1, carriage.getDate(), tableContentStyle);
		createCell(thirdRow, 2, "", tableContentStyle);
		sheet.addMergedRegion(new CellRangeAddress(5, 5, 1, 2));
		createCell(thirdRow, 3, "驾驶员", tableContentStyle);
		createCell(thirdRow, 4, carriage.toDriversString(), tableContentStyle);
		createCell(thirdRow, 5, "", tableContentStyle);
		sheet.addMergedRegion(new CellRangeAddress(5, 5, 4, 5));
		createCell(thirdRow, 6, "车牌号", tableContentStyle);
		createCell(thirdRow, 7, carriage.getVehLPN(), rightBorderBoldStyle);

		//4th row
		HSSFRow forthRow = sheet.createRow(6);
		forthRow.setHeightInPoints(rowHeight);
		createCell(forthRow, 0, "接运地址", leftBorderBoldStyle);
		createCell(forthRow, 1, carriage.getAddress(), tableContentStyle);
		CellRangeAddress addressRegsion = new CellRangeAddress(6, 6, 1, 7);
		sheet.addMergedRegion(addressRegsion);
		HSSFRegionUtil.setBorderRight(HSSFCellStyle.BORDER_THICK, addressRegsion, sheet, workbook);
		HSSFRegionUtil.setBorderBottom(HSSFCellStyle.BORDER_THIN, addressRegsion, sheet, workbook);

		//5th row
		HSSFRow fifthRow = sheet.createRow(7);
		fifthRow.setHeightInPoints(rowHeight);
		createCell(fifthRow, 0, "特殊要求", leftBorderBoldStyle);
		// createCell(fifthRow, 1, carriage.getSpecials(), tableContentStyle);
		CellRangeAddress specialRegion = new CellRangeAddress(7, 7, 1, 7);
		sheet.addMergedRegion(specialRegion);
		HSSFRegionUtil.setBorderRight(HSSFCellStyle.BORDER_THICK, specialRegion, sheet, workbook);
		HSSFRegionUtil.setBorderBottom(HSSFCellStyle.BORDER_THIN, specialRegion, sheet, workbook);

		//6th row
		HSSFCellStyle remarkContentCellStyle = workbook.createCellStyle();
		remarkContentCellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);
		remarkContentCellStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);

		HSSFRow sixthRow = sheet.createRow(8);
		sixthRow.setHeightInPoints(rowHeight);
		createCell(sixthRow, 0, "备注(应带的工具及用品):" + carriage.getRemark(),
				remarkContentCellStyle);
		CellRangeAddress remarkContentRegion = new CellRangeAddress(8, 10, 0, 7);
		sheet.addMergedRegion(remarkContentRegion);
		HSSFRegionUtil.setBorderBottom(HSSFCellStyle.BORDER_THICK, remarkContentRegion, sheet, workbook);
		HSSFRegionUtil.setBorderLeft(HSSFCellStyle.BORDER_THICK, remarkContentRegion, sheet, workbook);
		HSSFRegionUtil.setBorderRight(HSSFCellStyle.BORDER_THICK, remarkContentRegion, sheet, workbook);

		HSSFRow gatherRow = sheet.createRow(11);
		createCell(gatherRow, 5, "接尸员:", null);
		createCell(gatherRow, 6, carriage.toGathersString(), null);

		sheet.autoSizeColumn(0);

		return workbook;
	}

	private HSSFCellStyle getRightBorderBoldStyle(HSSFWorkbook workbook) {
		HSSFCellStyle rightBorderBoldStyle = workbook.createCellStyle();
		rightBorderBoldStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		rightBorderBoldStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		rightBorderBoldStyle.setBorderRight(HSSFCellStyle.BORDER_THICK);
		rightBorderBoldStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
		rightBorderBoldStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		rightBorderBoldStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		return rightBorderBoldStyle;
	}

	private HSSFCellStyle getLeftBorderBoldStyle(HSSFWorkbook workbook) {
		HSSFCellStyle s = workbook.createCellStyle();
		s.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		s.setBorderLeft(HSSFCellStyle.BORDER_THICK);
		s.setBorderRight(HSSFCellStyle.BORDER_THIN);
		s.setBorderTop(HSSFCellStyle.BORDER_THIN);
		s.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		s.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		return s;
	}

	private HSSFCellStyle getRightTopBorderBoldStyle(HSSFWorkbook workbook) {
		HSSFCellStyle rightTopBorderBoldStyle = workbook.createCellStyle();
		rightTopBorderBoldStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		rightTopBorderBoldStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		rightTopBorderBoldStyle.setBorderRight(HSSFCellStyle.BORDER_THICK);
		rightTopBorderBoldStyle.setBorderTop(HSSFCellStyle.BORDER_THICK);
		rightTopBorderBoldStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		rightTopBorderBoldStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		return rightTopBorderBoldStyle;
	}

	private HSSFCellStyle getTopBorderBoldStyle(HSSFWorkbook workbook) {
		HSSFCellStyle topBorderBoldStyle = workbook.createCellStyle();
		topBorderBoldStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		topBorderBoldStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		topBorderBoldStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
		topBorderBoldStyle.setBorderTop(HSSFCellStyle.BORDER_THICK);
		topBorderBoldStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		topBorderBoldStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		return topBorderBoldStyle;
	}

	private HSSFCellStyle getLeftRightBorderBoldStyle(HSSFWorkbook workbook) {
		HSSFCellStyle leftRightBoldCornerStyle = workbook.createCellStyle();
		leftRightBoldCornerStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		leftRightBoldCornerStyle.setBorderLeft(HSSFCellStyle.BORDER_THICK);
		leftRightBoldCornerStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
		leftRightBoldCornerStyle.setBorderTop(HSSFCellStyle.BORDER_THICK);
		leftRightBoldCornerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		leftRightBoldCornerStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		return leftRightBoldCornerStyle;
	}

	private HSSFCell createCell(HSSFRow row, int col, Object value, HSSFCellStyle style) {
		HSSFCell cell = row.createCell(col);
		String textValue = "";
		if (value instanceof Date) {
			Date date = (Date) value;
			SimpleDateFormat sdf = new SimpleDateFormat("yy年MM月dd日 HH:mm");
			textValue = sdf.format(date);
		} else if (value != null) {
			textValue = value.toString();
		}
		cell.setCellValue(textValue);
		if (style != null) {
			cell.setCellStyle(style);
		}
		return cell;
	}

	// TODO 注意分页的问题！！！
	public HSSFWorkbook genernateChargeReceipt(String title, Long id) {
		ChargeRecord chargeRecord = chargeRecordDao.get(id);

		HSSFWorkbook workbook = new HSSFWorkbook();
		HSSFSheet sheet = workbook.createSheet();

		//TITLE
		HSSFRow titleRow = sheet.createRow(0);
		HSSFCell titleCell = titleRow.createCell(0);
		titleCell.setCellValue(title);

		HSSFCellStyle titleStyle = workbook.createCellStyle();
		titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		HSSFFont titleFont = workbook.createFont();
		titleFont.setFontName("黑体");
		titleFont.setFontHeightInPoints((short) 16);
		titleStyle.setFont(titleFont);
		titleCell.setCellStyle(titleStyle);
		sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 6));

		//Header
		HSSFRow headerRow1 = sheet.createRow(1);
		createCell(headerRow1, 0, "业务编码:", null);
		final Registration registration = chargeRecord.getRegistration();
		createCell(headerRow1, 1, registration.getBizNo(), null);

		HSSFRow headerRow2 = sheet.createRow(2);
		StringBuilder basicInfo = new StringBuilder("收到:");
		basicInfo.append(registration.getDeadName());
		basicInfo.append(" ");
		basicInfo.append("性别:");
		basicInfo.append(CommonUtils.getDataItemDisplayText(2L, registration.getdGender()));
		basicInfo.append(" ");
		basicInfo.append("年龄:");
		basicInfo.append(registration.getdAge());
		basicInfo.append(" ");
		basicInfo.append("地址:");
		basicInfo.append(registration.getHomeAddr());
		basicInfo.append(" ");
		basicInfo.append("业务组:");
		basicInfo.append(registration.getBizTeam());
		basicInfo.append(" ");
		basicInfo.append("车号:");
		basicInfo.append(registration.getVehicleLpn());

		createCell(headerRow2, 0, basicInfo, null);

		//TABLE HEADER
		HSSFRow tableHeaderRow = sheet.createRow(3);
		int i = 0;
		createCell(tableHeaderRow, i++, "编码", getStyle(workbook));
		createCell(tableHeaderRow, i++, "收费项目名称", getStyle(workbook));
		createCell(tableHeaderRow, i++, "单位", getStyle(workbook));
		createCell(tableHeaderRow, i++, "数量", getStyle(workbook));
		createCell(tableHeaderRow, i++, "单价", getStyle(workbook));
		createCell(tableHeaderRow, i++, "金额", getStyle(workbook));
		createCell(tableHeaderRow, i++, "备注", getStyle(workbook));

		//TABLE BODY
		int rowStart = 4;
		List<ChargeItem> items = chargeRecord.getItems();
		for (ChargeItem item : items) {
			HSSFRow aRow = sheet.createRow(rowStart++);
			int j = 0;
			createCell(aRow, j++, item.getCode(), getStyle(workbook));
			createCell(aRow, j++, item.getDetailName(), getStyle(workbook));
			createCell(aRow, j++, item.getUnit(), getStyle(workbook));
			createCell(aRow, j++, item.getCount(), getStyle(workbook));
			createCell(aRow, j++, item.getSingleFee(), getStyle(workbook));
			createCell(aRow, j++, item.getTotalFee(), getStyle(workbook));
			createCell(aRow, j++, item.getRemark(), getStyle(workbook));
		}

		//TABLE FOOTER
		HSSFRow tableFooterRow = sheet.createRow(rowStart++);
		StringBuilder tableFooterValue = new StringBuilder("合计:");
		String amountToChinese = CurrencyUtil.amountToChinese(chargeRecord.getTotalFee());
		tableFooterValue.append(amountToChinese);
		tableFooterValue.append(" (");
		tableFooterValue.append(chargeRecord.getTotalFee());
		tableFooterValue.append(" )     ");
		tableFooterValue.append("实收金额:");
		String amountToPaidFee = CurrencyUtil.amountToChinese(chargeRecord.getPaidFee());
		tableFooterValue.append(amountToPaidFee);
		tableFooterValue.append(" (");
		tableFooterValue.append(chargeRecord.getPaidFee());
		tableFooterValue.append(" )     ");

		createCell(tableFooterRow, 0, tableFooterValue, null);

		HSSFRow attentionRow = sheet.createRow(rowStart++);
		HSSFCellStyle attentionStyle = workbook.createCellStyle();
		HSSFFont font2 = workbook.createFont();
		font2.setFontName("楷体");
		font2.setFontHeightInPoints((short) 7);
		attentionStyle.setFont(font2);
		createCell(attentionRow, 0,
				"注意事项:经办人签字时已确认逝者省无重要物品，同意火化，对上述费用无异议，凭费用清单火化，火化证领取骨灰",
				attentionStyle);
		//BUTTOM

		HSSFRow buttomRow = sheet.createRow(rowStart++);
		createCell(buttomRow, 0, "制表:", null);
		createCell(buttomRow, 1, chargeRecord.getOperator(), null);
		createCell(buttomRow, 3, "打印时间:", null);
		createCell(buttomRow, 4, new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date()), null);
		createCell(buttomRow, 5, "经办人签字:", null);

		CellRangeAddress cellRangeAddress = new CellRangeAddress(3, rowStart - 2, 0, 6);
		HSSFRegionUtil.setBorderLeft(HSSFCellStyle.BORDER_THICK, cellRangeAddress, sheet, workbook);
		HSSFRegionUtil.setBorderRight(HSSFCellStyle.BORDER_THICK, cellRangeAddress, sheet, workbook);
		HSSFRegionUtil.setBorderTop(HSSFCellStyle.BORDER_THICK, cellRangeAddress, sheet, workbook);
		HSSFRegionUtil.setBorderBottom(HSSFCellStyle.BORDER_THICK, cellRangeAddress, sheet, workbook);
		return workbook;
	}
}
